package com.btkjgc.service.impl;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.Enumeration;
import java.util.List;

import javax.servlet.http.HttpServletRequest;

import org.apache.commons.beanutils.PropertyUtils;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Service;
import org.springframework.web.servlet.ModelAndView;

import com.btkjgc.models.AccountInfo;
import com.btkjgc.models.AccountUser;
import com.btkjgc.models.AdminUser;
import com.btkjgc.models.AjaxObj;
import com.btkjgc.models.CarAccount;
import com.btkjgc.models.CarAccountLog;
import com.btkjgc.models.CarT;
import com.btkjgc.models.CartypePostage;
import com.btkjgc.models.CouponReceive;
import com.btkjgc.models.Dictionary;
import com.btkjgc.models.extend.AccountUserModel;
import com.btkjgc.models.extend.CarAccountModel;
import com.btkjgc.models.extend.CarModel;
import com.btkjgc.models.extend.CartypePostageModel;
import com.btkjgc.models.extend.ExchangeElectricRecordModel;
import com.btkjgc.service.intf.AccountUserManageService_Intf;
import com.btkjgc.utils.HibernateUtil;
import com.btkjgc.utils.JsonUtil;

@Service
public class AccountUserManageService_Impl extends BaseService_Impl implements AccountUserManageService_Intf
{
	
	static Logger logger = Logger.getLogger(AccountUserManageService_Impl.class);

	private SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	
	private SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd");

	@Override
	public void setListData(HttpServletRequest request, ModelAndView mv,int pageIndex, int totalRecord, 
			List<AccountUserModel> accountUserModel) {
		// TODO Auto-generated method stub
		
		Enumeration<String> names = request.getParameterNames();
		while (names.hasMoreElements())
		{
			String name = names.nextElement();
			mv.addObject(name, request.getParameter(name));
		}
		
		mv.addObject("account_users", accountUserModel);
		
		//账户类型列
		String typesql =" SELECT dt.`key`,dt.`value` FROM dictionary_t dt WHERE dt.`name`='AccType'";
		
		List<Dictionary> typeList = HibernateUtil.executeSQLQueryList(typesql,Dictionary.class);
		if(typeList.size() > 0)
		{
			mv.addObject("typeList", typeList);
		}
		
		//公司列
		String companysql = " select aut.name,CONCAT(id,'') 'key' from account_user_t aut where aut.type=1 and aut.del = 0";
		
		List<Dictionary> companyList = HibernateUtil.executeSQLQueryList(companysql,Dictionary.class);
		if (companyList.size() > 0)
		{
			mv.addObject("companyList", companyList);
		}
		
		//车型列
		String cartypesql = " select ct.name,CONCAT(id,'') 'key' from cartype_t ct where ct.del = 0";
		
		List<Dictionary> cartypeList = HibernateUtil.executeSQLQueryList(cartypesql,Dictionary.class);
		if(cartypeList.size() > 0)
		{
			mv.addObject("cartypeList", cartypeList);
		}
		
		//城市列
		String citysql = " select DISTINCT(ct.Name),ct.id 'key' from cities_t ct LEFT JOIN account_user_t aut ON aut.cityid=ct.id where aut.del=0";
		
		List<Dictionary> cityList = HibernateUtil.executeSQLQueryList(citysql,Dictionary.class);
		if(cityList.size() > 0)
		{
			mv.addObject("cityList", cityList);
		}
		
		//车辆状态列
		String statussql = " SELECT dt.`key`,dt.`value` FROM dictionary_t dt WHERE dt.`name`='CarStatus'";
		
		List<Dictionary> statusList = HibernateUtil.executeSQLQueryList(statussql, Dictionary.class);
		if(statusList.size() > 0)
		{
			mv.addObject("statusList", statusList);
		}
		
		//优惠券列
		String couponsql = " select ct.name,CONCAT(id,'') 'key' " +
				           " from coupon_t ct " +
				           " where ct.del = 0 and ct.status = 1 and ct.point = 1 " +
				           " and (now()>=ifnull(usebegindate,'0000-00-00 00:00::00'))" +
				           " and (now()<=ifnull(useenddate,'9999-00-00 00:00::00')) ";
		
		List<Dictionary> couponList = HibernateUtil.executeSQLQueryList(couponsql, Dictionary.class);
		if(couponList.size() > 0)
		{
			mv.addObject("couponList", couponList);
		}
		
		//常规计费列
		String consql = " SELECT cpt.name,CONCAT(id,'') 'key' FROM conventional_postage_t cpt WHERE cpt.del=0 AND cpt.status =1\n" +
				 		" AND cpt.beginTime<=(select now()) AND cpt.endTime>=(select now()) UNION ALL\n" +
				 		" SELECT cpt.name,CONCAT(id, '') 'key' FROM conventional_postage_t cpt WHERE cpt.del = 0 AND cpt.status = 1\n" +
				 		" AND ISNULL(cpt.beginTime) AND ISNULL(cpt.endTime)";
		
		List<Dictionary> conList = HibernateUtil.executeSQLQueryList(consql, Dictionary.class);
		if(conList.size() > 0)
		{
			mv.addObject("conList", conList);
		}
		
		//套餐计费列
		String packsql = " SELECT ppt.name,CONCAT(id,'') 'key' FROM package_postage_t ppt WHERE ppt.del=0 AND ppt.status =1\n" +
						 " AND IFNULL(ppt.beginTime,'0000-00-00 00:00:00')<=(select now()) AND IFNULL(ppt.endTime,'9999-99-99 99:99:99')>=(select now()) UNION ALL\n" +
						 " SELECT ppt.name,CONCAT(id, '') 'key' FROM package_postage_t ppt WHERE ppt.del = 0 AND ppt.status = 1\n" +
						 " AND ISNULL(ppt.beginTime) AND ISNULL(ppt.endTime)";
		
		List<Dictionary> packList = HibernateUtil.executeSQLQueryList(packsql,Dictionary.class);
		if(packList.size() > 0)
		{
			mv.addObject("packList", packList);
		}
		
		mv.addObject("currentpage", pageIndex);
		mv.addObject("totalRecord", totalRecord);
		
	}


	@SuppressWarnings("unchecked")
	@Override
	public Integer insertAccountUser(AccountUserModel accountUserModel) {
		// TODO Auto-generated method stub
		AccountUser accountUser = new AccountUser();
		String d=new SimpleDateFormat("yyyyMMddHHmmss").format(Calendar.getInstance().getTime());
		
		try{
			PropertyUtils.copyProperties(accountUser, accountUserModel);
			//设置操作员、操作时间
			AdminUser loginUser = getLoginUser();
			accountUser.setOperatorid(loginUser.getId());
			accountUser.setOperdate(new Date());
			Integer id=HibernateUtil.save(accountUser);
			//如果是个人用户，插入个人车辆表
			if(accountUserModel.getType()==0){
				CarAccount carAccount = new CarAccount();
				carAccount.setAccountid(id);
				carAccount.setDel(1);
				carAccount.setCreatedate(new Date());
				HibernateUtil.save(carAccount);
			}
			//初始account_info_t表的数据
			insertAccountInfo(id);
			//设置account
			int length = id.toString().length();
			String account = "";
			account = account+d;
			for(int i=0;i<8-length;i++){
				account+="0";
			}
			account=account+id;
			String hql = " update from AccountUser set account='"+account+"' where id="+id;
			HibernateUtil.executeUpdate(hql);
			
			return 1;
		}catch(Exception e){
			return -11;
		}
	}


	@Override
	public void updatePrivateAccountUser(AccountUserModel accountUserModel) {
		// TODO Auto-generated method stub
		
		AdminUser loginUser = getLoginUser();
		
		String hql = " update from AccountUser " +
					 " set" +
					 " name='"+accountUserModel.getName()+"',"+
					 " tel='"+accountUserModel.getTel()+"',"+
					 " identityid='"+accountUserModel.getIdentityid()+"',"+
					 " provinceid="+accountUserModel.getProvinceid()+","+
					 " cityid="+accountUserModel.getCityid()+","+
					 " operatorid=" +loginUser.getId()+","+
				  	 " operdate='" +df.format(new Date())+"'"+
					 " where id="+accountUserModel.getId();
		
		HibernateUtil.executeUpdate(hql);
	}


	@Override
	public AjaxObj updateCompanyAccountUser(AccountUserModel accountUserModel) {
		// TODO Auto-generated method stub
		AjaxObj ajaxObj = new AjaxObj();
		ajaxObj.setResult(true);
		
		try{
			AdminUser loginUser = getLoginUser();
			
			String hql = " update from AccountUser " +
						 " set" +
						 " name='"+accountUserModel.getCname()+"',"+
						 " tel='"+accountUserModel.getCtel()+"',"+
						 " bank_num='"+accountUserModel.getBank_num()+"',"+
						 " bank_account='"+accountUserModel.getBank_account()+"',"+
						 " company='"+accountUserModel.getCname()+"',"+
						 " comment='"+accountUserModel.getComment()+"',"+
						 " operatorid=" +loginUser.getId()+","+
					  	 " operdate='" +df.format(new Date())+"'"+
						 " where id="+accountUserModel.getId();
			
			HibernateUtil.executeUpdate(hql);
		}catch(Exception e){
			e.printStackTrace();
			ajaxObj.setResult(false);
			ajaxObj.setMsg("修改时发生错误");
		}
		
		return ajaxObj;
	}


	@Override
	public void deleteById(Integer accountUserId) 
	{
		AdminUser loginUser = getLoginUser();
		
		List hqlList=new ArrayList<>();
		String hql = " update from AccountUser set del=1,"+
					 " operatorid=" +loginUser.getId()+","+
					 " operdate='" +df.format(new Date())+"'"+
					 " where id=?";
		hqlList.add(hql);
		
		List paramLists=new ArrayList<>();
		List paramLists2 =new ArrayList<>();
		paramLists2.add(String.valueOf(accountUserId));
		paramLists.add(paramLists2);
		paramLists.add(paramLists2);
		HibernateUtil.batchExecuteUpdate(hqlList, paramLists);
		
	}


	@SuppressWarnings("unchecked")
	@Override
	public void insertCoupon(ArrayList<CouponReceive> coupons) {
		// TODO Auto-generated method stub
		for(int i=0;i<coupons.size();i++){
			HibernateUtil.save(coupons.get(i));
			//同时修改account_info_t表的数据
			String hql1 = " select cp.amount from Coupon cp where id ="+coupons.get(i).getCouponid();
			String available = " select ai.available_balance from AccountInfo ai where accountid="+coupons.get(i).getAccountid();
			String coupon = " select ai.coupon_num from AccountInfo ai where accountid="+coupons.get(i).getAccountid();
			int availableBalance = HibernateUtil.executeQueryCount(hql1);
			int base1 = HibernateUtil.executeQueryCount(available);//可用余额基数
			int base2 = HibernateUtil.executeQueryCount(coupon);//个人用户优惠券数量基数
			int total1 = availableBalance+base1;
			int total2 = base2+1;
			String hql2 = " update from AccountInfo " +
					 	  " set" +
					 	  " available_balance="+total1+","+
					 	  " coupon_num="+total2+
					 	  " where accountid="+coupons.get(i).getAccountid();
			HibernateUtil.executeUpdate(hql2);
			
			AdminUser loginUser = getLoginUser();
			String hql3 = " update from AccountUser " +
						  " set" +
						  " operatorid=" +loginUser.getId()+","+
					  	  " operdate='" +df.format(new Date())+"'"+
						  " where id=" +coupons.get(i).getAccountid();
			HibernateUtil.executeUpdate(hql3);
		}
		
	}

	@Override
	public void insertAccountInfo(Integer mid) {
		// 向account_info_t表初始化数据
		AccountInfo accountInfo = new AccountInfo();
		accountInfo.setAccountid(mid);
		accountInfo.setAvailable_balance(0);
		accountInfo.setTotal_back_balance(0);
		accountInfo.setTotal_consum(0);
		accountInfo.setTotal_recharge(0);
		accountInfo.setTotal_refund(0);
		accountInfo.setCoupon_num(0);
		accountInfo.setTotal_score(0);
		accountInfo.setTotal_free_trace(0);
		
		HibernateUtil.save(accountInfo);
		
	}


	@Override
	public void updatePostage(CartypePostage cartypePostage) {
		// TODO Auto-generated method stub
		
		String hql = "update from CartypePostage set postage_type="+cartypePostage.getPostage_type()+","+
					 "postage_id="+cartypePostage.getPostage_id()+
					 "where cartype="+cartypePostage.getCartype()+
					 "and accountid='"+cartypePostage.getAccountid()+"'";
		
		AdminUser loginUser = getLoginUser();
		String hql3 = " update from AccountUser " +
				  	  " set" +
				  	  " operatorid=" +loginUser.getId()+","+
				  	  " operdate='" +df.format(new Date())+"'"+
				  	  " where id=" +cartypePostage.getAccountid();
		HibernateUtil.executeUpdate(hql3);
		
		HibernateUtil.executeUpdate(hql);
		
		//如果是公司账号，为该公司下的所有个人账户修改资费
		String sql = "select type from account_user_t where id="+cartypePostage.getAccountid();
		Integer type = HibernateUtil.executeSQLQueryInt(sql);
		if(type==1){//公司账号
			String caridsql = "select id from car_t where companyid="+cartypePostage.getAccountid();
			List<CarModel> caridList = HibernateUtil.executeSQLQueryList(caridsql, CarModel.class);
			for(int i=0;i<caridList.size();i++){
				String accountidsql = "select accountid from car_account_t where carid="+caridList.get(i).getId();
				List<CarAccount> accountidList = HibernateUtil.executeSQLQueryList(accountidsql, CarAccount.class);
				if(accountidList.size()>1){
					for(int j=0;j<accountidList.size();j++){
						String Phql = "update from CartypePostage set postage_type="+cartypePostage.getPostage_type()+","+
							 	  	  "postage_id="+cartypePostage.getPostage_id()+
							 	  	  "where cartype="+cartypePostage.getCartype()+
							 	  	  "and accountid='"+accountidList.get(j).getAccountid()+"'";
					
						HibernateUtil.executeUpdate(Phql);
					}
				}else if(accountidList.size()!=0||!accountidList.isEmpty()){
					Integer accountid = HibernateUtil.executeSQLQueryInt(accountidsql);
					String Phql = "update from CartypePostage set postage_type="+cartypePostage.getPostage_type()+","+
					 	  	  	  "postage_id="+cartypePostage.getPostage_id()+
					 	  	  	  "where cartype="+cartypePostage.getCartype()+
					 	  	  	  "and accountid='"+accountid+"'";
					
					HibernateUtil.executeUpdate(Phql);
				}
				
			}
		}
		
	}

	@Override
	public void insertPostage(CartypePostage cartypePostage) {
		// TODO Auto-generated method stub
		
		HibernateUtil.save(cartypePostage);
		
		AdminUser loginUser = getLoginUser();
		String hql3 = " update from AccountUser " +
			  	  	  " set" +
			  	  	  " operatorid=" +loginUser.getId()+","+
			  	  	  " operdate='" +df.format(new Date())+"'"+
			  	  	  " where id=" +cartypePostage.getAccountid();
		HibernateUtil.executeUpdate(hql3);
		
		//如果是公司账号，为该公司下的所有个人账户匹配资费
		String sql = "select type from account_user_t where id="+cartypePostage.getAccountid();
		Integer type = HibernateUtil.executeSQLQueryInt(sql);
		if(type==1){//公司账号
			String caridsql = "select id from car_t where companyid="+cartypePostage.getAccountid();
			List<CarModel> caridList = HibernateUtil.executeSQLQueryList(caridsql, CarModel.class);
			for(int i=0;i<caridList.size();i++){
				String accountidsql = "select accountid from car_account_t where carid="+caridList.get(i).getId();
				List<CarAccount> accountidList = HibernateUtil.executeSQLQueryList(accountidsql, CarAccount.class);
				if(accountidList.size()>1){
					for(int j=0;j<accountidList.size();j++){
						CartypePostage cartypePostageP = new CartypePostage();
						cartypePostageP.setAccountid(accountidList.get(j).getAccountid());
						cartypePostageP.setCartype(cartypePostage.getCartype());
						cartypePostageP.setPostage_id(cartypePostage.getPostage_id());
						cartypePostageP.setPostage_type(cartypePostage.getPostage_type());
						
						HibernateUtil.save(cartypePostageP);
					}
				}else if(accountidList.size()!=0||!accountidList.isEmpty()){
					Integer accountid = HibernateUtil.executeSQLQueryInt(accountidsql);
					CartypePostage cartypePostageP = new CartypePostage();
					cartypePostageP.setAccountid(accountid);
					cartypePostageP.setCartype(cartypePostage.getCartype());
					cartypePostageP.setPostage_id(cartypePostage.getPostage_id());
					cartypePostageP.setPostage_type(cartypePostage.getPostage_type());
					
					HibernateUtil.save(cartypePostageP);
				}
				
			}
		}
		
	}

	@Override
	public AjaxObj bindCar(CarAccountModel carAccountModel) {
		// TODO Auto-generated method stub
		
		AjaxObj ajaxObj=new AjaxObj();
		ajaxObj.setResult(true);
		String hql1;
		CarAccountLog carAccountLog = new CarAccountLog();
		
		try{
			//设置car_account_t表数据
			if(carAccountModel.getSelf()==1){//自有车
				hql1 = " update from CarAccount set self=1," +
					   " del=0," +
					   " createdate='" +df.format(new Date())+"',"+
					   " carbrand='" +carAccountModel.getSelfBrand()+"',"+
					   " cartypeid=" +carAccountModel.getCartype()+
					   " where accountid=" +carAccountModel.getId();
				//设置car_account_log_t数据
				carAccountLog.setDel(0);
				carAccountLog.setAccountid(carAccountModel.getId());
				carAccountLog.setCreatedate(new Date());
				carAccountLog.setCartypeid(carAccountModel.getCartype());
				carAccountLog.setCarbrand(carAccountModel.getSelfBrand());
				carAccountLog.setSelf(1);
			}else{//非自有车
				hql1 = " update from CarAccount set self=0," +
					   " carid=" +carAccountModel.getCarid()+","+
					   " del=0," +
					   " createdate='" +df.format(new Date())+"',"+
					   " carbrand='" +carAccountModel.getCarbrand()+"',"+
					   " cartypeid=" +carAccountModel.getCartypeid()+
					   " where accountid=" +carAccountModel.getId();
				//设置car_account_log_t数据
				carAccountLog.setDel(0);
				carAccountLog.setAccountid(carAccountModel.getId());
				carAccountLog.setCreatedate(new Date());
				carAccountLog.setCarid(carAccountModel.getCarid());
				carAccountLog.setCartypeid(carAccountModel.getCartypeid());
				carAccountLog.setCarbrand(carAccountModel.getCarbrand());
				carAccountLog.setSelf(0);
				//设置所属公司
				String sql = "select aut.* from account_user_t aut LEFT JOIN car_t ct ON ct.companyid=aut.id where ct.id="+carAccountModel.getCarid();
				AccountUserModel company = (AccountUserModel)HibernateUtil.executeSQLQueryObject(sql, AccountUserModel.class);
				String hql2 = " update from AccountUser au set au.company='"+company.getName()+"' where au.id="+carAccountModel.getId();
				HibernateUtil.executeUpdate(hql2);
				//如果所属公司已添加套餐，为该账户新增套餐
				String cptsql = "select cpt.* from cartype_postage_t cpt where cpt.accountid="+company.getId()+" and cpt.cartype="+carAccountModel.getCartypeid();
				CartypePostageModel cartypePostageModel = (CartypePostageModel)HibernateUtil.executeSQLQueryObject(cptsql, CartypePostageModel.class);
				if(cartypePostageModel!=null){
					CartypePostage cartypePostage = new CartypePostage();
					cartypePostage.setCartype(carAccountModel.getCartypeid());
					cartypePostage.setPostage_type(cartypePostageModel.getPostage_type());
					cartypePostage.setPostage_id(cartypePostageModel.getPostage_id());
					cartypePostage.setAccountid(carAccountModel.getId());
					
					HibernateUtil.save(cartypePostage);
				}
			}
			HibernateUtil.executeUpdate(hql1);
			//保存car_account_log_t数据
			HibernateUtil.save(carAccountLog);
			//更新初始里程
			String carhql = " update from CarT set init_miles="+carAccountModel.getInitmile()+" where id="+carAccountModel.getCarid();
			HibernateUtil.executeUpdate(carhql);
		}catch(Exception e) {
			e.printStackTrace();
			ajaxObj.setResult(false);
			ajaxObj.setMsg("绑定时出错");
		}
		return ajaxObj;
	}

	@Override
	public AjaxObj unbindCar(CarAccountModel carAccountModel) {
		// TODO Auto-generated method stub
		
		AjaxObj ajaxObj=new AjaxObj();
		ajaxObj.setResult(true);
		
		try{
			if(carAccountModel.getSelf()==1){//自有车
				String selfhql = " update from CarAccount set del=2," +
						 		 " unbindreason='" +carAccountModel.getUnbindreason()+"',"+
						 		 " unbinddate='" +sf.format(new Date())+"'"+
						 		 " where accountid=" +carAccountModel.getId();
				
				HibernateUtil.executeUpdate(selfhql);
			}else{//非自有车
				String sql = " select * from car_t where id=" +carAccountModel.getCarid();
				CarT car = (CarT)HibernateUtil.executeSQLQueryObject(sql, CarT.class);
				
				String hql = " update from CarAccount set del=2," +
							 " unbindreason='" +carAccountModel.getUnbindreason()+"',"+
							 " unbinddate='" +sf.format(new Date())+"'"+
							 " where carid=" +carAccountModel.getCarid();
				
				HibernateUtil.executeUpdate(hql);
				//退车，如果该车末次免费，将islast置1否则置2
				if(car.getFree()==1||car.getFree()==3){
					String hql2 = " update from CarT set islast=1 where id=" +carAccountModel.getCarid();
					
					HibernateUtil.executeUpdate(hql2);
				}
			}
		}catch(Exception e) {
			e.printStackTrace();
			ajaxObj.setResult(false);
			ajaxObj.setMsg("解绑时出错");
		}
		return ajaxObj;
	}

	@Override
	public AjaxObj immediateUnbindCar(CarAccountModel carAccountModel) {
		// TODO Auto-generated method stub
		
		AjaxObj ajaxObj=new AjaxObj();
		ajaxObj.setResult(true);
		
		try{
			if(carAccountModel.getSelf()==1){//自有车
				String hql = " update from CarAccount set carid=null,del=1,cartypeid=null,carbrand=null where accountid="+carAccountModel.getId();
				
				HibernateUtil.executeUpdate(hql);
				//更新car_account_log_t表
				String loghql = " update from CarAccountLog set del=1,unbinddate='"+df.format(new Date())+"' where accountid="+carAccountModel.getId()+" and del=0";
				
				HibernateUtil.executeUpdate(loghql);
				
				String sql = " select * from cartype_postage_t where accountid="+carAccountModel.getId();
				CartypePostage cartypePostage = (CartypePostage)HibernateUtil.executeSQLQueryObject(sql, CartypePostage.class);
				if(cartypePostage!=null){
					HibernateUtil.delete(cartypePostage);
				}
				
			}else{//非自有车
				String hql1 = " update from CarAccount set carid=null,del=1,cartypeid=null,carbrand=null where accountid="+carAccountModel.getId();
				
				HibernateUtil.executeUpdate(hql1);
				//更新car_account_log_t表
				String loghql = " update from CarAccountLog set del=1,unbinddate='"+df.format(new Date())+"' where accountid="+carAccountModel.getId()+" and del=0";
				
				HibernateUtil.executeUpdate(loghql);
				//同时删除该账户所属公司及套餐
				String hql2 = " update from AccountUser set company=null where id="+carAccountModel.getId();
				
				HibernateUtil.executeUpdate(hql2);
				
				String sql1 = " select * from cartype_postage_t where accountid="+carAccountModel.getId();
				CartypePostage cartypePostage = (CartypePostage)HibernateUtil.executeSQLQueryObject(sql1, CartypePostage.class);
				
				HibernateUtil.delete(cartypePostage);
			}
		}catch(Exception e) {
			e.printStackTrace();
			ajaxObj.setResult(true);
		}
		return ajaxObj;
	}
	
}